<?php
require_once '../include/config.php';
require_once '../include/utils.class.php';
require_once '../include/orm.class.php';
$orm = new ORM();
$orm2 = new ORM(DB_HOST, $orm->get_db() . '2', DB_LOGIN, DB_PASS, 'latin1');
$table_prefix = 'pb_colg_';

$attr = 'Tables_in_' . $orm2->get_db();
$tstmt = $orm2->prepare('SHOW TABLES');
$tstmt->execute();
$data = new stdClass();
$i = 0;
while ($i < $tstmt->rowCount()) {
  $tname = $tstmt->fetch(PDO::FETCH_OBJ)->$attr;
  $sql = 'DROP TABLE IF EXISTS `' . $orm2->get_db() . '`.`' . $tname . '`';
  $orm2->exec($sql);
  $i++;
};

try {
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "template`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `d_offer` varchar(512) DEFAULT '',
            `d_offer_n` varchar(64) DEFAULT '',
            `d_cop` varchar(512) DEFAULT '',
            `d_cop_n` varchar(64) DEFAULT '',
            `d_transcript` varchar(512) DEFAULT '',
            `d_transcript_n` varchar(64) DEFAULT '',
            `l_offer` varchar(512) DEFAULT '',
            `l_offer_n` varchar(512) DEFAULT '',
            `l_cop` varchar(512) DEFAULT '',
            `l_cop_n` varchar(64) DEFAULT '',
            `l_transcript` varchar(512) DEFAULT '',
            `l_transcript_n` varchar(64) DEFAULT '',
            `n_offer` varchar(512) DEFAULT '',
            `n_offer_n` varchar(64) DEFAULT '',
            `n_cop` varchar(512) DEFAULT '',
            `n_cop_n` varchar(64) DEFAULT '',
            `n_transcript` varchar(512) DEFAULT '',
            `n_transcript_n` varchar(64) DEFAULT '',
            `c_attendance` varchar(512) DEFAULT '',
            `c_attendance_n` varchar(64) DEFAULT '',
            `c_verbal` varchar(512) DEFAULT '',
            `c_verbal_n` varchar(64) DEFAULT '',
            `c_1st` varchar(512) DEFAULT '',
            `c_1st_n` varchar(64) DEFAULT '',
            `c_2nd` varchar(512) DEFAULT '',
            `c_2nd_n` varchar(64) DEFAULT '',
            `f_inv` varchar(512) DEFAULT '',
            `f_inv_n` varchar(64) DEFAULT '',
            `f_receipt` varchar(512) DEFAULT '',
            `f_receipt_n` varchar(64) DEFAULT ''
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "user`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `account` varchar(64) UNIQUE DEFAULT '',
            `password` varchar(64) DEFAULT '',
            `first_name` varchar(256) DEFAULT '',
            `last_name` varchar(256) DEFAULT '',
            `email` varchar(256) DEFAULT '',
            `role` varchar(64) DEFAULT '" . UT_MARKETING . "',
            `prog_mods` text DEFAULT NULL
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "teacher`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `first_name` varchar(256) DEFAULT '',
            `last_name` varchar(256) DEFAULT '',
            `email` varchar(256) DEFAULT ''
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "agent`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `agent` varchar(64) DEFAULT '',
            `first_name` varchar(256) DEFAULT '',
            `last_name` varchar(256) DEFAULT '',
            `company` varchar(256) DEFAULT '',
            `address` text,
            `postal_address` text,
            `phone` varchar(256) DEFAULT '',
            `mobile` varchar(256) DEFAULT '',
            `email` varchar(256) DEFAULT '',
            `fax` varchar(256) DEFAULT '',
            `country` varchar(64) DEFAULT '',
            `user_id` varchar(64) DEFAULT '',
            `comment` text,
            `bank_account_name` varchar(256) DEFAULT '',
            `bank_account_no` varchar(256) DEFAULT '',
            `bank_name` varchar(256) DEFAULT '',
            `bank_branch_name` varchar(256) DEFAULT '',
            `bank_branch_address` varchar(256) DEFAULT '',
            `agent_type` tinyint(1) DEFAULT 0 COMMENT '(0=GST,1=IRD,2=Oversea)',
            `agent_gst_no` varchar(64) DEFAULT '',
            `agent_ird_no` varchar(64) DEFAULT '',
            `commission_type` tinyint(1) DEFAULT 0 COMMENT '(0=percentage,1=fix_rate)',
            `commission_percentage` float DEFAULT 0,
            `commission_fixedrate` decimal(10, 2) DEFAULT 0
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "unit`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `no` int(11) DEFAULT 0,
            `cat` varchar(32) DEFAULT '',
            `ver` varchar(32) DEFAULT '',
            `name` varchar(256) DEFAULT '',
            `lvl` varchar(32) DEFAULT '',
            `credits` smallint(6) DEFAULT 0
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
    
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "classroom`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `name` varchar(256) DEFAULT ''
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
    
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "program`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `type` varchar(32) DEFAULT '" . PT_DIPLOMA . "',
            `name` varchar(256) DEFAULT '',
            `formal_name` varchar(256) DEFAULT '',
            `len` varchar(256) DEFAULT '',
            `unit` varchar(256) DEFAULT '',
            `comment` text DEFAULT NULL,
            `reqs` text DEFAULT NULL
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
    
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "course`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `code` varchar(256) DEFAULT '',
            `name` varchar(256) DEFAULT '',
            `comment` text DEFAULT NULL,
            `title__` varchar(256) DEFAULT ''
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
    
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "intake`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `type` varchar(32) DEFAULT '" . PT_DIPLOMA . "',
            `type_ncea` varchar(32) DEFAULT '',
            `start` date DEFAULT '0000-00-00',
            `end` date DEFAULT '0000-00-00',
            `title__` varchar(256) DEFAULT '',
            `programs__` varchar(256) DEFAULT ''
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
    
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "class`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `type` varchar(32) DEFAULT '" . PT_DIPLOMA . "',
            `name` varchar(256) DEFAULT '',
            `course_id` int DEFAULT 0,
            `course_name` varchar(256) DEFAULT '',
            `intake_id` int DEFAULT 0,
            `teacher_id` int DEFAULT 0,
            `teacher_name` varchar(256) DEFAULT '',
            `room_id` int DEFAULT 0,
            `room_name` varchar(256) DEFAULT '',
            `times` text DEFAULT NULL
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
    
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "student`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `date` date DEFAULT '0000-00-00',
            `user_id` int DEFAULT 0,
            `user_title__` varchar(512) DEFAULT '',
            `id_number` int UNIQUE DEFAULT 0,
            `first_name` varchar(32) DEFAULT '',
            `last_name` varchar(32) DEFAULT '',
            `preferred_name` varchar(32) DEFAULT '',
            `gender` tinyint(1) DEFAULT 0,
            `date_of_birth` date DEFAULT '0000-00-00',
            `citizenship` varchar(32) DEFAULT '',
            `ethnic_group` varchar(32) DEFAULT '',
            `nsn_id` varchar(32) DEFAULT '',
            `phone` varchar(32) DEFAULT '',
            `mobile` varchar(32) DEFAULT '',
            `email` varchar(64) DEFAULT '',
            `fax` varchar(32) DEFAULT '',
            `facebook_id` varchar(32) DEFAULT '',
            `address` text DEFAULT NULL,
            `visa_type` varchar(32) DEFAULT '',
            `visa_expiry` date DEFAULT '0000-00-00',
            `insurance_com` varchar(256) DEFAULT '',
            `insurance_expiry` date DEFAULT '0000-00-00',
            `insurance_number` varchar(64) DEFAULT '',
            `passport_no` varchar(32) DEFAULT '',
            `passport_expiry` date DEFAULT '0000-00-00',
            `parents_name` varchar(32) DEFAULT '',
            `parents_phone` varchar(32) DEFAULT '',
            `parents_mobile` varchar(32) DEFAULT '',
            `parents_email` varchar(64) DEFAULT '',
            `parents_address` text DEFAULT NULL,
            `emergency_name` varchar(32) DEFAULT '',
            `emergency_phone` varchar(32) DEFAULT '',
            `emergency_mobile` varchar(32) DEFAULT '',
            `emergency_email` varchar(64) DEFAULT '',
            `emergency_address` text DEFAULT NULL,
            `medical_condition` text DEFAULT NULL,
            `note` text DEFAULT NULL,
            `title__` varchar(256) DEFAULT '',
            `balance__` decimal(10,2) DEFAULT 0.00
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
    
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "enrollment`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `track_id` varchar(16) UNIQUE DEFAULT '',
            `date` date DEFAULT '0000-00-00',
            `user_id` int DEFAULT 0,
            `user_title__` varchar(512) DEFAULT '',
            `agent_id` int DEFAULT 0,
            `agent_title__` varchar(512) DEFAULT '',
            `student_id` int DEFAULT 0,
            `student_title__` varchar(512) DEFAULT '',
            `enrolled_program_id` int DEFAULT 0,
            `program_id` int DEFAULT 0,
            `program_name` varchar(256) DEFAULT '',
            `program_type` varchar(32) DEFAULT '" . PT_DIPLOMA . "',
            `reg_date` date DEFAULT '0000-00-00',
            `total_academic` decimal(10,2) DEFAULT 0.00,
            `start_academic` date DEFAULT '0000-00-00',
            `end_academic` date DEFAULT '0000-00-00',
            `weeks_academic` int DEFAULT 0,
            `total_accommodation` decimal(10,2) DEFAULT 0.00,
            `start_accommodation` date DEFAULT '0000-00-00',
            `end_accommodation` date DEFAULT '0000-00-00',
            `weeks_accommodation` int DEFAULT 0,
            `price_accommodation` decimal(10,2) DEFAULT 0.00,
            `total_insurance` decimal(10,2) DEFAULT 0.00,
            `start_insurance` date DEFAULT '0000-00-00',
            `end_insurance` date DEFAULT '0000-00-00',
            `months_insurance` int DEFAULT 0,
            `intakes__` text DEFAULT NULL,
            `papers` int DEFAULT 0,
            `papers__` int DEFAULT 0,
            `fee_admin` decimal(10,2) DEFAULT 0.00,
            `fee_pickup` decimal(10,2) DEFAULT 0.00,
            `fee_exam` decimal(10,2) DEFAULT 0.00,
            `fee_homestay` decimal(10,2) DEFAULT 0.00,
            `fee_living` decimal(10,2) DEFAULT 0.00,
            `fee_material` decimal(10,2) DEFAULT 0.00,
            `fee_registration` decimal(10,2) DEFAULT 0.00,
            `fee_other` decimal(10,2) DEFAULT 0.00,
            `note` text DEFAULT NULL,
            `enrl_type_note` varchar(512) DEFAULT '',
            `balance__` decimal(10,2) DEFAULT 0.00,
            `is_paid` tinyint(1) DEFAULT 0,
            `is_enrolled` tinyint(1) DEFAULT 0,
            `is_current` tinyint(1) DEFAULT 0,
            `is_withdrawn` tinyint(1) DEFAULT 0,
            `is_cancel` tinyint(1) DEFAULT 0,
            `is_trial` tinyint(1) DEFAULT 0,
            `fl_enrollment_form` varchar(256) DEFAULT '',
            `fl_contact_form` varchar(256) DEFAULT '',
            `fl_agreement_form` varchar(256) DEFAULT '',
            `fl_trustaccount_form` varchar(256) DEFAULT '',
            `fl_offer` varchar(256) DEFAULT '',
            `fl_confirmation` varchar(256) DEFAULT '',
            `fl_receipt` varchar(256) DEFAULT '',
            `fl_passport` varchar(256) DEFAULT '',
            `fl_visa` varchar(256) DEFAULT '',
            `fl_ielts` varchar(256) DEFAULT '',
            `fl_academic` varchar(256) DEFAULT '',
            `fl_certificate` varchar(256) DEFAULT '',
            `fl_insurance` varchar(256) DEFAULT '',
            `fl_under18` varchar(256) DEFAULT '',
            `fl_passport_no` varchar(32) DEFAULT '',
            `fl_passport_expiry` date DEFAULT '0000-00-00',
            `fl_visa_type` varchar(32) DEFAULT '',
            `fl_visa_expiry` date DEFAULT '0000-00-00',
            `fl_insurance_com` varchar(256) DEFAULT '',
            `fl_insurance_expiry` date DEFAULT '0000-00-00',
            `fl_insurance_number` varchar(64) DEFAULT '',
            `fl_note` varchar(256) DEFAULT ''
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "enrolled_program`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `program_id` int DEFAULT 0,
            `program_name` varchar(256) DEFAULT '',
            `program_type` varchar(32) DEFAULT '" . PT_DIPLOMA . "',
            `student_id` int DEFAULT 0,
            `progress__` float DEFAULT 0.00,
            `status` tinyint(1) DEFAULT 0
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "enrolled_intake`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `type__` varchar(256) DEFAULT '" . PT_DIPLOMA . "',
            `type_ncea__` varchar(256) DEFAULT '',
            `enrollment_id` int DEFAULT 0,
            `enrolled_program_id` int DEFAULT 0,
            `student_id` int DEFAULT 0,
            `intake_id` int DEFAULT 0,
            `start` date DEFAULT '0000-00-00',
            `end` date DEFAULT '0000-00-00',
            `papers` int DEFAULT 0,
            `w1` date DEFAULT '0000-00-00',
            `w2` date DEFAULT '0000-00-00',
            `w3` date DEFAULT '0000-00-00',
            `note` text DEFAULT NULL
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "enrolled_class`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `type__` varchar(256) DEFAULT '" . PT_DIPLOMA . "',
            `type_ncea__` varchar(256) DEFAULT '',
            `student_id` int DEFAULT 0,
            `enrollment_id` int DEFAULT 0,
            `intake_id` int DEFAULT 0,
            `class_id` int DEFAULT 0,
            `class_name` varchar(256) DEFAULT '',
            `course_id` int DEFAULT 0,
            `course_name` varchar(256) DEFAULT '',
            `enrolled_program_id` int DEFAULT 0,
            `enrolled_intake_id` int DEFAULT 0,
            `start` date DEFAULT '0000-00-00',
            `end` date DEFAULT '0000-00-00',
            `status` tinyint(1) DEFAULT 0,
            `result__` float DEFAULT -2,
            `result2__` varchar(256) DEFAULT ''
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "invoice`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `balance__` decimal(10,2) DEFAULT 0.00,
            `to_trustaccount` tinyint(1) DEFAULT 0,
            `track_id` varchar(16) UNIQUE DEFAULT '',
            `student_id` int DEFAULT 0,
            `student_title__` varchar(512) DEFAULT '',
            `user_id` int DEFAULT 0,
            `user_title__` varchar(512) DEFAULT '',
            `enrollment_id` int DEFAULT 0,
            `enrollment_track_id` varchar(16) DEFAULT '',
            `particular` varchar(512) DEFAULT '',
            `date` date DEFAULT '0000-00-00',
            `due_date` date DEFAULT '0000-00-00',
            `amount` decimal(10,2) DEFAULT 0.00,
            `received__` decimal(10,2) DEFAULT 0.00,
            `from` date DEFAULT '0000-00-00',
            `to` date DEFAULT '0000-00-00',
            `comment` text DEFAULT null
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "payment`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `to_trustaccount` tinyint(1) DEFAULT 0,
            `invoice_id` int DEFAULT 0,
            `invoice_track_id` varchar(16) DEFAULT '',
            `student_id` int DEFAULT 0,
            `student_title__` varchar(512) DEFAULT '',
            `user_id` int DEFAULT 0,
            `user_title__` varchar(512) DEFAULT '',
            `enrollment_id` int DEFAULT 0,
            `enrollment_track_id` varchar(16) DEFAULT '',
            `particular` varchar(512) DEFAULT '',
            `date` date DEFAULT '0000-00-00',
            `amount` decimal(10,2) DEFAULT 0.00,
            `discount` decimal(10,2) DEFAULT 0.00,
            `from` date DEFAULT '0000-00-00',
            `to` date DEFAULT '0000-00-00',
            `comment` text DEFAULT null
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "atdn`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `atdnlog_id` int DEFAULT 0,
            `student_id` int DEFAULT 0,
            `class_id` int DEFAULT 0,
            `enrolled_program_id` int DEFAULT 0,
            `enrolled_intake_id` int DEFAULT 0,
            `enrolled_class_id` int DEFAULT 0,
            `signin_at` datetime DEFAULT '0000-00-00 00:00:00',
            `status` tinyint(1) DEFAULT 0,
            `is_absent` tinyint(1) DEFAULT 0,
            `is_tutorial` tinyint(1) DEFAULT 0
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "atdnlog`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `class_id` int DEFAULT 0,
            `start` date DEFAULT '0000-00-00',
            `add_at` date DEFAULT '0000-00-00'
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "rslt`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `rsltlog_id` int DEFAULT 0,
            `ncea_unit_id` int DEFAULT 0,
            `student_id` int DEFAULT 0,
            `class_id` int DEFAULT 0,
            `hd_track_id` varchar(16) DEFAULT '',
            `score` float DEFAULT 0,
            `resit__` tinyint(1) DEFAULT 0
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
  $sql = "CREATE TABLE IF NOT EXISTS `" . $table_prefix . "rsltlog`(
            `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `class_id` int DEFAULT 0,
            `ncea_unit_id` int DEFAULT 0,
            `date` date DEFAULT '0000-00-00',
            `header__` text DEFAULT NULL,
            `add_at` date DEFAULT '0000-00-00'
          ) ENGINE=MYISAM;";
  $orm2->exec($sql);
  
//  $orm2->exec("INSERT INTO " . $table_prefix . "user values (NULL, 'admin', '" . md5(123456) . "', 'Weida', 'Xue', 'luckyweida@gmail.com', '" . UT_MANAGEMENT . "', '')");
} catch (PDOException $e) {
  Utils::dump($e);
}
?>